﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CodeCamp12.Domain;
using System.Data.SqlClient;
using System.Configuration;
using log4net;

namespace CodeCamp12.DAL
{
    public class AnnouncementDataManagement
    {
        private ConnectionStringProvider connString = new ConnectionStringProvider();
        private ILog log = LogManager.GetLogger("CodeCamp12.DAL");

        #region Announcements
        
        /// <summary>
        /// Insert new Announcement in the database
        /// </summary>
        /// <param name="announcement"></param>
        public void InsertAnnouncement(Announcements announcement)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("InsertAnnouncement", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};

            comm.Parameters.AddWithValue("@Title", announcement.Title);
            comm.Parameters.AddWithValue("@Date", announcement.Date);
            comm.Parameters.AddWithValue("@UserID", announcement.UserID);
            comm.Parameters.AddWithValue("@Description", announcement.Description);

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
            
        }

        /// <summary>
        /// Update existing announcement in the database
        /// </summary>
        /// <param name="announcement"></param>
        public void UpdateAnnouncement(Announcements announcement)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("UpdateAnnouncement", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};

            comm.Parameters.AddWithValue("@Title", announcement.Title);
            comm.Parameters.AddWithValue("@Date", announcement.Date);
            comm.Parameters.AddWithValue("@UserID", announcement.UserID);
            comm.Parameters.AddWithValue("@Description", announcement.Description);
            comm.Parameters.AddWithValue("@ID", announcement.ID);
            
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// Delete announcement from the database
        /// </summary>
        /// <param name="announcement"></param>
        public void DeleteAnnouncement(Announcements announcement)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("DeleteAnnouncement", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@ID", announcement.ID);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

        }


        /// <summary>
        /// Get all existing announcements from the database
        /// </summary>
        /// <returns>Announcements</returns>
        public List<Announcements> GetAllAnnouncements()
        {
            var announcementList = new List<Announcements>();
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAllAnnouncements", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};

            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    var announcement = new Announcements
                                           {
                                               ID = (Guid) reader["ID"],
                                               Title = reader["Title"].ToString(),
                                               Date = Convert.ToDateTime(reader["Date"]),
                                               UserID = (Guid) reader["UserID"],
                                               Description = reader["Description"].ToString()
                                           };
                    announcementList.Add(announcement);

                }
                reader.Close();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
            return announcementList;
        }


        /// <summary>
        /// Get the announcement with Id passed as parametar
        /// </summary>
        /// <param name="id"></param>
        /// <returns>Announcement</returns>
        public Announcements GetAnnouncementById(Guid id)
        {
            var announcement = new Announcements();
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAnnouncementsById", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", id);
            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                if (reader.Read())
                {
                    announcement.Title = reader["Title"].ToString();
                    announcement.Date = Convert.ToDateTime(reader["Date"]);
                    announcement.Description = reader["Description"].ToString();
                    announcement.UserName = reader["UserName"].ToString();
                }
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

            return announcement;
        }
        
        #endregion

        #region Notifications
        
        /// <summary>
        /// Insert new homepage notification
        /// </summary>
        /// <param name="notification"></param>
        public void InsertNotification(Notification notification)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("InsertNotification", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};

            comm.Parameters.AddWithValue("@Title", notification.Title);
            comm.Parameters.AddWithValue("@Date", notification.Date);
            comm.Parameters.AddWithValue("@UserID", notification.UserID);
            comm.Parameters.AddWithValue("@Description", notification.Description);
            comm.Parameters.AddWithValue("@Link", notification.Link);

            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }
        }

        /// <summary>
        /// Delete existing home page notification
        /// </summary>
        /// <param name="notification"></param>
        public void DeleteNotification(Notification notification)
        {
            var conn = connString.GetConnection();
            var comm = new SqlCommand("DeleteNotification", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};
            comm.Parameters.AddWithValue("@id", notification.ID);
            try
            {
                conn.Open();
                comm.ExecuteNonQuery();
            }
            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }
            finally
            {
                conn.Close();
            }

        }

        /// <summary>
        /// Get all notifications from database
        /// </summary>
        /// <returns>Notification</returns>
        public List<Notification> GetAllNotifications()
        {
            var notificationList = new List<Notification>();
            var conn = connString.GetConnection();
            var comm = new SqlCommand("GetAllNotifications", conn)
                           {CommandType = System.Data.CommandType.StoredProcedure};

            try
            {
                conn.Open();
                var reader = comm.ExecuteReader();
                while (reader.Read())
                {
                    var notification = new Notification
                                           {
                                               ID = new Guid(reader["ID"].ToString()),
                                               Title = reader["Title"].ToString(),
                                               Date = Convert.ToDateTime(reader["Date"]),
                                               UserID = new Guid(reader["UserID"].ToString()),
                                               Description = reader["Description"].ToString(),
                                               Link = reader["Link"].ToString()
                                           };
                    notificationList.Add(notification);
                }
                reader.Close();
            }

            catch(Exception ex)
            {
                 
                log.Error(ex);
                throw;
            }

            finally
            {
                conn.Close();
            }

            return notificationList;
        }

        #endregion
    }
}
